package com.itic.appbase.applications.exports;

import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

import com.itic.appbase.applications.exports.utils.PoiHelper;
import com.itic.appbase.framework.utils.StringHelper;

/**
 * 不使用模板导出Excel
 * <p>
 * Company: itic
 * </p>
 * 
 * @author: shijun
 * @date: 2015年6月1日 下午5:24:07
 * @version: V1.0
 */
@SuppressWarnings("unchecked")
public class ExportExcelWithoutTemplet extends ExportExcel {
    // 定义一个sheet表所能创建的最大行数，理论上为65536
    // 考虑到向下合并单元格的行数不确定，所以定义最大行数为60000，5536行为浮动行数
    private static final int MAXROWNUM = 60000;
    // 默认单元格列宽15
    private static final int COLWIDTH = 15;

    @Override
    protected void exportAutoExcel(Map<String, Object> mapConfig,
            HttpServletRequest request, OutputStream outputStream) {

        if (null != mapConfig && !mapConfig.isEmpty()) {
            Object titleObj = mapConfig.get("title");// 标题数据
            Object theadObj = mapConfig.get("theadData");// 表头数据
            Object tbodyObj = mapConfig.get("tbodyData");// 主体数据
            List<List<CellConfig>> titleList = null == theadObj ? null
                    : (List<List<CellConfig>>) theadObj;
            List<List<CellConfig>> bodyList = null == tbodyObj ? null
                    : (List<List<CellConfig>>) tbodyObj;
            CellConfig titleCellConfig = null == titleObj ? null
                    : (CellConfig) titleObj;
            // 加载Excel数据
            loadDataOnExcel(titleCellConfig, titleList, bodyList, outputStream);
        } else {
            log.info("未配置导出数据，请配置相关数据");
        }
    }

    /**
     * 加载excel数据
     * 
     * @param title 标题
     * @param dataList  EXCEL数据
     * @param outputStream
     */
    private void loadDataOnExcel(CellConfig titleCellConfig,
            List<List<CellConfig>> titleList, List<List<CellConfig>> dataList,
            OutputStream outputStream) {
        
        int sheetNum = 0;// 所需工作表的数量
        if (null != dataList && !dataList.isEmpty()) {
            sheetNum = (int) Math.ceil((double) (titleList.size() + dataList.size()) / MAXROWNUM);// 一个工作表最大容量为60000
        }
        sheetNum = sheetNum == 0 ? 1 : sheetNum;

        HSSFWorkbook workBook = PoiHelper.createWorkBook();// 创建Excel工作薄

        HSSFFont baseFont = PoiHelper.creatFont(workBook);// 创建单元格基础字体样式

        HSSFCellStyle baseCellStyle = PoiHelper.createCellStyle(workBook,
                baseFont);
        int dataIndexTemp = 0;// 变量：主体数据list索引变量
        for (int i = 0; i < sheetNum; i++) {// 循环遍历创建工作表
            int rowIndexTemp = 0;// 变量：记录行坐标数据
            HSSFSheet sheet = PoiHelper.createSheet(workBook);// 创建工作表
            sheet.setDefaultColumnWidth(COLWIDTH);// 设置工作表默认行宽为15
            // 冻结前三行，前两列，上下滚动的时候冻结前三行,左右滚动的时候冻结前两列
            // sheet.createFreezePane(2, 3, 2, 3);

            // 创建Excel标题
            rowIndexTemp = createTitle(titleCellConfig, workBook, 
                    rowIndexTemp, sheet);
            // 创建Excel的thead标题
            rowIndexTemp = createThead(titleList, workBook, baseFont,
                    rowIndexTemp, sheet);
            int titleindexTemp = rowIndexTemp;// EXCEL表格标题行所占的行数
            // 创建Excel的tbody数据
            dataIndexTemp = createTbody(dataList, workBook, baseCellStyle,
                    dataIndexTemp, rowIndexTemp, titleindexTemp, sheet);
        }
        try {
            workBook.write(outputStream);
        } catch (IOException e) {
            log.info("Excel导出异常",e);
        }
    }

    /**
     * 创建Excel的body
     * 
     * @param dataList
     * @param workBook
     * @param baseCellStyle
     * @param dataIndexTemp
     * @param rowIndexTemp
     * @param sheet
     * @return
     */
    private int createTbody(List<List<CellConfig>> dataList,
            HSSFWorkbook workBook, HSSFCellStyle baseCellStyle,
            int dataIndexTemp, int rowIndexTemp, int titleindexTemp,
            HSSFSheet sheet) {
        
        if (null != dataList && !dataList.isEmpty()) {
            int sheetNum = workBook.getNumberOfSheets();// 获取工作薄中工作表sheet的数量
            int startIndex = sheetNum > 1 ? ((sheetNum - 1) * (MAXROWNUM - titleindexTemp))
                    : 0;
            // 遍历表格主体body数据
            for (int m = dataIndexTemp; m < dataList.size(); m++) {
                List<CellConfig> cellConfigList = dataList.get(m);// 获取每行的列数据
                // 创建行中的列
                for (int k = 0; k < cellConfigList.size(); k++) {
                    CellConfig cellConfig = cellConfigList.get(k);
                    if (null == cellConfig.getFirstRow()
                            || null == cellConfig.getLastRow()
                            || null == cellConfig.getFirstCol()
                            || null == cellConfig.getLastCol()) {
                        log.info("单元格范围值不能为空");
                        continue;
                    } else if (cellConfig.getFirstRow() > cellConfig
                            .getLastRow()
                            || cellConfig.getFirstCol() > cellConfig
                                    .getLastCol()) {
                        log.info("单元格范围值错误，开始行不能大于结束行，开始列不能大于结束列");
                        continue;
                    }
                    int firstRow = cellConfig.getFirstRow(), lastRow = cellConfig
                            .getLastRow(), firstCol = cellConfig.getFirstCol(), lastCol = cellConfig
                            .getLastCol();

                    HSSFRow row = PoiHelper
                            .getRow(sheet, firstRow - startIndex);
                    if (null == row) {// 创建行
                        row = PoiHelper.createRow(sheet, firstRow - startIndex);
                    }
                    if (firstRow < lastRow || firstCol < lastCol) {// 开始行小于结束行或者开始列小于结束列，则合并单元格
                        // 合并单元格
                        CellRangeAddress cellRangeAddress = PoiHelper
                                .setRangeAddress(firstRow - startIndex, lastRow
                                        - startIndex, firstCol, lastCol);
                        PoiHelper.setRangeBorder(workBook, sheet,
                                cellRangeAddress);
                        PoiHelper.mergedRegion(sheet, cellRangeAddress);
                    }

                    // 单元格赋值
                    HSSFCell cell = PoiHelper.getCell(row, firstCol);
                    if (cell == null) {// 创建列
                        cell = PoiHelper.createCell(row, firstCol);
                    }
                    // 设置列的值以及样式
                    PoiHelper.setCellValue(cell, cellConfig.getCellValue(),
                            baseCellStyle);
                    rowIndexTemp = rowIndexTemp >= (lastRow - startIndex) ? rowIndexTemp
                            : (lastRow - startIndex);// 获取下一行的行坐标
                }
                dataIndexTemp++;
                // 如果到一张表的最大容量，则创建下一张工作表
                if (rowIndexTemp != 0 && rowIndexTemp % MAXROWNUM == 0) {
                    break;
                }
            }
        } else {
            log.info("Excel主体数据为空");
        }
        log.info("Excel的行索引：" + rowIndexTemp + ",主体tbody中的行数据索引："
                + dataIndexTemp);
        return dataIndexTemp;
    }

    /**
     * 设置Excel的head标题
     * 
     * @param dataList
     * @param workBook
     * @param baseFont
     * @param rowIndexTemp
     * @param sheet
     */
    private int createThead(List<List<CellConfig>> dataList,
            HSSFWorkbook workBook, HSSFFont baseFont, int rowIndexTemp,
            HSSFSheet sheet) {
        if (null != dataList && !dataList.isEmpty()) {
            // 设置单元格标题样式
            HSSFCellStyle headTitleStyle = setHeadTitleStyle(workBook);
            // 遍历标题行数据
            for (int j = 0; j < dataList.size(); j++) {
                List<CellConfig> cellConfigList = dataList.get(j);// 获取每行的列数据
                // 创建行中的列
                for (int k = 0; k < cellConfigList.size(); k++) {
                    CellConfig cellConfig = cellConfigList.get(k);
                    if (null == cellConfig.getFirstRow()
                            || null == cellConfig.getLastRow()
                            || null == cellConfig.getFirstCol()
                            || null == cellConfig.getLastCol()) {
                        log.info("单元格范围值不能为空");
                        continue;
                    } else if (cellConfig.getFirstRow() > cellConfig
                            .getLastRow()
                            || cellConfig.getFirstCol() > cellConfig
                                    .getLastCol()) {
                        log.info("单元格范围值错误，开始行不能大于结束行，开始列不能大于结束列");
                        continue;
                    }
                    int firstRow = cellConfig.getFirstRow(), lastRow = cellConfig
                            .getLastRow(), firstCol = cellConfig.getFirstCol(), lastCol = cellConfig
                            .getLastCol();
                    HSSFRow row = PoiHelper.getRow(sheet, firstRow);
                    if (null == row) {// 创建行
                        row = PoiHelper.createRow(sheet, firstRow);
                    }

                    if (firstRow < lastRow || firstCol < lastCol) {// 开始行小于结束行或者开始列小于结束列，则合并单元格
                        // 合并单元格
                        CellRangeAddress cellRangeAddress = PoiHelper
                                .setRangeAddress(firstRow, lastRow, firstCol,
                                        lastCol);
                        PoiHelper.setRangeBorder(workBook, sheet,
                                cellRangeAddress);
                        PoiHelper.mergedRegion(sheet, cellRangeAddress);
                    }
                    // 单元格赋值
                    HSSFCell cell = PoiHelper.getCell(row, firstCol);
                    if (cell == null) {// 创建列
                        cell = PoiHelper.createCell(row, firstCol);
                    }
                    // 设置列的值以及样式
                    PoiHelper.setCellValue(cell, cellConfig.getCellValue(),
                            headTitleStyle);

                    if (null != cellConfig.getColWidth()) { // 设置列的宽度,若无设置，则宽度自适应
                        PoiHelper.setColumnWidth(sheet, firstCol,
                                cellConfig.getColWidth());
                    }
                    /*
                     * else{//自适应宽度,消耗时间较长，使用默认列宽COLWIDTH
                     * sheet.autoSizeColumn(firstCol,true); }
                     */
                    rowIndexTemp = rowIndexTemp >= lastRow ? rowIndexTemp
                            : lastRow;// 获取下一行的行坐标
                }
            }
        } else {
            log.info("标题行数据为空");
        }
        log.info("Excel的行索引：" + rowIndexTemp);
        return rowIndexTemp;
    }

    /**
     * 设置Excel标题
     * 
     * @param title
     * @param workBook
     * @param baseFont
     * @param rowIndexTemp
     * @param sheet
     * @return
     */
    private int createTitle(CellConfig titleCellConfig, HSSFWorkbook workBook,
             int rowIndexTemp, HSSFSheet sheet) {
        if (null != titleCellConfig) {// 创建Excel大标题
            if (StringHelper.isNotBlank(titleCellConfig.getCellValue())) {
                if (null == titleCellConfig.getFirstRow()
                        || null == titleCellConfig.getLastRow()
                        || null == titleCellConfig.getFirstCol()
                        || null == titleCellConfig.getLastCol()) {
                    log.info("单元格范围值设置错误，默认为0:0:0:3");
                    titleCellConfig.setFirstRow(rowIndexTemp);
                    titleCellConfig.setLastRow(rowIndexTemp);
                    titleCellConfig.setFirstCol(0);
                    titleCellConfig.setLastCol(3);
                } else if (titleCellConfig.getFirstRow() > titleCellConfig
                        .getLastRow()
                        || titleCellConfig.getFirstCol() > titleCellConfig
                                .getLastCol()) {
                    log.info("单元格范围值错误，开始行不能大于结束行，开始列不能大于结束列");
                    return rowIndexTemp;
                }

                HSSFRow row = PoiHelper.createRow(sheet,
                        titleCellConfig.getFirstRow());// 创建行
                HSSFCell cell = PoiHelper.createCell(row,
                        titleCellConfig.getFirstCol());// 创建列

                // 合并标题行--可配置
                CellRangeAddress cellRangeAddress = PoiHelper.setRangeAddress(
                        titleCellConfig.getFirstRow(),
                        titleCellConfig.getLastRow(),
                        titleCellConfig.getFirstCol(),
                        titleCellConfig.getLastCol());
                PoiHelper.mergedRegion(sheet, cellRangeAddress);
                // 给合并后的单元格赋值并设置样式
                PoiHelper.setCellValue(cell, titleCellConfig.getCellValue(),
                        setTitleStyle(workBook));
                rowIndexTemp = rowIndexTemp >= titleCellConfig.getLastRow() ? rowIndexTemp
                        : titleCellConfig.getLastRow();
            }
        }
        log.info("Excel的行索引：" + rowIndexTemp);
        return rowIndexTemp;
    }

    /**
     * 设置单元格的大标题样式
     * 
     * @param workBook
     * @param baseFont
     * @return
     */
    private HSSFCellStyle setTitleStyle(HSSFWorkbook workBook) {
        HSSFFont font = PoiHelper.creatFont(workBook);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short) 18);
        return PoiHelper.createCellStyle(workBook, font);
    }

    /**
     * 设置单元格headTitle标题样式
     * 
     * @param workBook
     * @param font
     * @return
     */
    private HSSFCellStyle setHeadTitleStyle(HSSFWorkbook workBook) {
        HSSFFont font = PoiHelper.creatFont(workBook);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short) 14);
        return PoiHelper.createCellStyle(workBook, font);
    }
}
